package com.apes.fn.ssm.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.ssm.model.CalculatedState;
import com.apes.fn.ssm.model.StoreMemberModelsImport;
import com.apes.fn.ssm.repository.*;
import com.apes.fn.ssm.repository.StoreMemberModelsImportRepository;
import com.apes.framework.api.DomainService;
import com.apes.framework.api.SpringManager;
import com.apes.framework.jpa.Connector;
import com.apes.framework.jpa.generator.repository.SequenceRepository;
import com.apes.framework.jpa.spec.JpaDsl;
import com.apes.framework.plugin.esb.api.SimpleRequest;
import com.apes.framework.plugin.event.Listener;
import com.apes.framework.util.MapUtil;
import com.apes.framework.util.ReflectUtil;
import com.apes.pi.service.PiManager;
import com.apes.scm.masterdata.stock.repository.DcDistributionRangeRepository;
import com.apes.scm.masterdata.stock.repository.LocationRepository;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.lang.reflect.Array;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

@Service("safetyStockCalcuService")
public class SafetyStockCalcuService extends DomainService {
    @Autowired
    private Connector connector;
    @Autowired
    private DcDistributionRangeRepository dcDisRepository;
    @Autowired
    private StoreMemberModelsRepository storeMemberModelsRepository;
    @Autowired
    private BrandPriorityRepository brandPriorityRepository;
    @Autowired
    private StockCalcuResultRepository stockCalcuResultRepository;
    @Autowired
    private CalculatedStateRepository calculatedStateRepository;


    private final List columns = Arrays.asList("carLicense", "jekunModelCode", "storeCode", "vendorUsers", "updatedAt", "id");
    private final Map columnIndex = MapUtil.mapper("carLicense", 1, "jekunModelCode", 3, "storeCode", 4, "updatedAt", 7, "id", 8, "vendorUsers", MapUtil.mapper("activity", 5, "mobile", 6));


    // 此处思考如何使一个类达到集成多个类的效果
    // exceptionA extends Exception，SQLException
    public void saveModel(List locationIds) {
        JSONArray safetyStockCacuDCArray = this.queryDCLocation(locationIds);
        this.insertModel(safetyStockCacuDCArray);
    }

    /**
     * 功能：保存车型数据
     * 获取 STOCK_STORE_MEMBER_MODELS 序列编码
     * */
    private synchronized String querySequnceId() {
        return SpringManager.getBean(SequenceRepository.class).findAll("SELECT IR_SEQUENCE_02333.NEXTVAL FROM DUAL ").get(0).toString();
    }

    /**
     * 功能：保存车型数据
     * 插入车型数据到数据库
     * */
    private void insertModel(JSONArray safetyStockCacuDCArray) {
        Connection conn = connector.getConnection();
        AtomicReference<PreparedStatement> prePackage = new AtomicReference<>();
//        Map<String, JSONArray> appModelCache = new ConcurrentHashMap<>(); // 思考 -----> 怎么让 map 不写在这里达到获取和赋值的效果， 失败类：putAppModelCache、obtainAppModelCache
        try {
            conn.setAutoCommit(false);
            prePackage.set(conn.prepareStatement(insertModelSql()));
            this.appendModelData("", safetyStockCacuDCArray, prePackage, conn);
        } catch (Exception  e) {
            e.printStackTrace();
        } finally {
            try {
                prePackage.get().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 功能：保存车型数据
     * 增量同步车型数据获取时间戳
     * */
    private Long queryMaxUpdateAt() {
        String sql = " SELECT MAX(UPDATED_AT) FROM STOCK_STORE_MEMBER_MODELS ";
        List updateAts = storeMemberModelsRepository.findAll(sql);
        if (updateAts.size() > 0 && updateAts.get(0) != null) {
            return Long.valueOf(updateAts.get(0).toString());
        }
        return 0L;
    }

    /**
     * 功能：保存车型数据
     * 拼接要保存到数据的车型数据
     * */
    private void appendModelData(String currentLocationId, JSONArray safetyStockCacuDCArray, AtomicReference<PreparedStatement> prePackage, Connection conn) {
        safetyStockCacuDCArray.forEach(singleDC -> {
            JSONObject dcObj = (JSONObject) singleDC;
            String cLocationId = dcObj.getString("C_LOCATION_ID");
            this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("VIP_MODEL", 1, "LOCATION_ID", cLocationId)); // 修改完成标记

            String storeCode = dcObj.getString("STORE_ID");
            String depotLevel = dcObj.getString("DEPOT_LEVEL");
            if ("000123".equals(depotLevel) || "000121".equals(depotLevel)) {
                JSONArray qryChildLocationDataArray = this.queryDCLocation(cLocationId);
                this.appendModelData(cLocationId, qryChildLocationDataArray, prePackage, conn);
            }
            // 获取 app 车型
            this.multipleVipModelAndSave(storeCode, Arrays.asList(obtainUpdateAt(cLocationId)), currentLocationId, cLocationId, prePackage, conn);


            this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("VIP_MODEL", 2, "LOCATION_ID", cLocationId)); // 修改完成标记
        });
    }

    // 获取仓库时间戳
    public long obtainUpdateAt(String locationId) {
        long updateAt = 0L;
        List updateAts = storeMemberModelsRepository.findAll(" SELECT NVL(MAX(UPDATED_AT), 0) FROM STOCK_STORE_MEMBER_MODELS WHERE LOCATION_ID = '"+ locationId +"' ");
        if (updateAts.size() > 0) updateAt = Long.valueOf(updateAts.get(0).toString());
        return updateAt;
    }

    // 分页查询车型数据, 在此进行递归
    private void multipleVipModelAndSave(String storeCode, List updateAt, String currentLocationId, String cLocationId, AtomicReference<PreparedStatement> prePackage, Connection conn) {
        JSONArray realTimeGetAppModel = this.goAppGetModel(storeCode, Long.valueOf(updateAt.get(0).toString()));
        long updatedAt = this.putParamValue(realTimeGetAppModel, prePackage, StringUtils.isEmpty(currentLocationId)?cLocationId:currentLocationId, conn);
        updateAt.set(0, updatedAt);

        if (realTimeGetAppModel != null && realTimeGetAppModel.size() != 0) {
            this.multipleVipModelAndSave(storeCode, updateAt, currentLocationId, cLocationId, prePackage, conn);
        }
    }

    /**
     * 功能：保存车型数据
     * 查询 dc 数据
     * */
    private JSONArray queryDCLocation(Object cLocationId) {
        List<Map> qryDCLocations = new ArrayList<>();
        if (cLocationId instanceof String) {
            qryDCLocations = dcDisRepository.safetyStockCalcuQueryDC((String) cLocationId);
        } else {
            qryDCLocations = SpringManager.getBean(LocationRepository.class).safetyStockCalcuQuery((List) cLocationId);
        }
        return (JSONArray) JSON.toJSON(qryDCLocations);
    }

    // 无效
    private Map putAppModelCache(String storeId, JSONArray appModelArray, String type) {
        AtomicReference<Map<String, JSONArray>> appModelCache2;
        AtomicReference<Map<String, JSONArray>> appModelCache = new AtomicReference<>();
        Map appModelCacheM = appModelCache.get();
        if ("put".equals(type)) {
            if (appModelCacheM == null || appModelCacheM.size() == 0) appModelCache.set(new HashedMap()); // 考虑使用 ConcurrentHashMap
            appModelCache.get().put(storeId, appModelArray);
        }

        return appModelCache.get();
    }

    // 无效
    private Map obtainAppModelCache(String storeId) {
        return this.putAppModelCache(storeId, null, "get");
    }

    /**
     * 功能：保存车型数据
     * insert 赋值 value
     * */
    private long putParamValue(JSONArray appModelArray, AtomicReference<PreparedStatement> prePackage, String cLocationId, Connection conn) {
        long updateAt = 0L;
        try {
            for (int i = 0, iLen = appModelArray.size(); i < iLen; i++) {
                JSONObject appModelObj = appModelArray.getJSONObject(i);

                if (appModelArray.size() - 1 == i) {
                    updateAt = appModelObj.getLong("updatedAt");
                }

                for (int j = 0, jLen = columns.size(); j < jLen; j++) {

                    if ("vendorUsers".equals(columns.get(j))) {
                        Map vendorUsersItem = (Map) columnIndex.get(columns.get(j));
                        JSONArray vendorUsersArray = appModelObj.getJSONArray("vendorUsers");
                        vendorUsersItem.forEach((k, v) -> {
                            try {
                                if (vendorUsersArray.size() != 0) {
                                    prePackage.get().setObject((Integer) v, vendorUsersArray.getJSONObject(0).get(k));
                                } else {
                                    prePackage.get().setObject((Integer) v, "");
                                }
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        });
                    } else {
                        prePackage.get().setObject((Integer) columnIndex.get(columns.get(j)), appModelObj.get(columns.get(j)));
                    }
                }
                prePackage.get().setObject(2, cLocationId);
                prePackage.get().addBatch();
            }

            prePackage.get().executeBatch(); // 执行时如有相同数据，使用触发器 TR_STOCK_CACU 删除
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return updateAt;
    }

    /**
     * 功能：保存车型数据
     * 保存 sql
     * */
    private String insertModelSql() {
        String id = this.sequenceName("STOCK_STORE_MEMBER_MODELS");
        String sql = "" +
                "INSERT INTO STOCK_STORE_MEMBER_MODELS " +
                "  (ID, CAR_LICENSE, " +
                "   LOCATION_ID, JEKUN_MODEL_CODE, STORE_CODE, VIP_LIVENESS_ID, MOBILE, UPDATED_AT, APP_UNIQUE_ID, CREATE_DATE, LAST_UPDATED_STAMP) " +
                "VALUES " +
                "  ("+ id +", ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, to_timestamp(to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')) ";
        return sql;
    }

    /**
     * 功能：保存车型数据
     * 去 app 抓取车型数据
     */
    private JSONArray goAppGetModel(String storeCode, long updateAt) {
        if (StringUtils.isEmpty(storeCode)) return new JSONArray();
        JSONArray filterParamArray = new JSONArray();

        JSONObject filterParamObj = new JSONObject();
        filterParamObj.put("storeCode", storeCode); // 需要更改格式为 Group 对象， 增加 updateAt 字段
        filterParamArray.add(filterParamObj);

        JSONObject updateAtObj = new JSONObject();
        JSONObject operator = new JSONObject();
        operator.put("gt", updateAt);
        updateAtObj.put("updatedAt", operator);
        filterParamArray.add(updateAtObj);


        JSONObject operatorO = new JSONObject();
        operatorO.put("and", filterParamArray);

        JSONObject condi = new JSONObject();
        condi.put("filter", operatorO);
        condi.put("perPage", 1000);
        condi.put("page", 1);
        condi.put("sort", "updatedAt");

        JSONObject allCondiO = new JSONObject();
        allCondiO.put("query", condi);
        allCondiO.put("fields", "id,storeCode,storeName,carLicense,vin,jekunModelCode,carModelLabel,vendorUsers.mobile,vendorUsers.isDefault,vendorUsers.activity, updatedAt");


        Map data = mapper("body", allCondiO, "headers", MapUtil.mapper("method", "v1/car-archive/index", "action", "GET"));
        Object result = invoke("pi.app.ext", data);
        JSONObject changeResult = JSONObject.parseObject(result.toString());
        if (changeResult.getBooleanValue("success")) return changeResult.getJSONArray("data");
        return new JSONArray();
    }

    // 测算车型 - 启动安全库存测算
    public void calcuStock(SimpleRequest request) {
        List isStarteds = calculatedStateRepository.findAll("SELECT ID FROM MAS_CALCULATE_PRODUCT WHERE ROWNUM = 1");
        if (isStarteds.size() != 0) return;

        // 老系统更新品类扩展表
        this.sendAynEvent("event:ssm.service.updateOldPLKZXX", null);

        // 创建车型结果表
        this.createStockResult();

        JSONObject paramC = request.getJO();
        List locationIds = paramC.getJSONArray("locationItems").stream().map(singleArray -> {return ((JSONObject) singleArray).getString("id");}).collect(Collectors.toList());
        List vipLocations = SpringManager.getBean(StoreMemberModelsImportRepository.class).findAll(" SELECT DISTINCT LOCATION_ID FROM STOCK_STORE_MEMBER_MODELS_I A WHERE 1 = 1 ");
        locationIds.removeAll(vipLocations);
        if (locationIds.size() != 0) this.saveModel(locationIds);

        // 基础数据准备
        stockCalcuResultRepository.exec("begin P_READY_MAIN_DATA(); end;");

        // 等待 app 车型数据执行完
        while(vipModelIsDone() > 0) {
            try {
                Thread.sleep(20000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

        this.createProcedure();
    }

    @Listener(topic = "event:ssm.service.updateOldPLKZXX")
    public void updateOldPLKZXX() {
        SpringManager.getBean(PiManager.class).synErp("scmAutoZnbhService.insertZnbhAqkc", new JSONObject());
    }

    // 测算车型 - 仓库异步请求测算数据中心数据
    public void createProcedure() {
        // 重新计算未完成的会员 - 超过 2 会员还没有完成表示会员更新失败或系统升级中断
        List existsUnfishVipDatas = stockCalcuResultRepository.findAll(this.unfishVip(1));
        if (existsUnfishVipDatas.size() != 0) {
            stockCalcuResultRepository.exec(this.unfishVip(2));
            List locationIds = stockCalcuResultRepository.findAll(this.unfishVip(3));
            this.saveModel(locationIds);
        }

        // 所有会员完成开始计算车型配置数据
        if (this.returnCalcuLocation()) {
            this.sendAynEvent("event:ssm.service.calcuCXDZML", null);
            this.sendAynEvent("event:ssm.service.calcuPPGZ", null);
        }
    }

    // 获取vip车型失败
    private String unfishVip(int step) {
        String sql = "";
        switch (step) {
            case 1:
                sql = " SELECT A.ID " +
                        "  FROM MAS_CALCULATED_STATE A " +
                        " WHERE VIP_MODEL != 2 " +
                        "   AND TO_DATE(TO_CHAR(LAST_UPDATED_STAMP, 'YYYY/MM/DD HH24:MI:SS'), " +
                        "               'YYYY/MM/DD HH24:MI:SS') + 150 / 24 / 60 < SYSDATE ";
                break;
            case 2:
                sql = " UPDATE MAS_CALCULATED_STATE SET LAST_UPDATED_STAMP = SYSDATE, VIP_MODEL = 0 WHERE VIP_MODEL != 2 ";
                break;
            case 3:
                sql = " SELECT LOCATION_ID FROM MAS_CALCULATED_STATE WHERE VIP_MODEL = 0 ";
                break;
            default:
        }
        return sql;
    }

    @Listener(topic = "event:ssm.service.calcuCXDZML")
    public void calcuCXDZML(SimpleRequest request) {
        stockCalcuResultRepository.exec("begin P_CALCU_CXDZML(); end;");
    }

    @Listener(topic = "event:ssm.service.calcuPPGZ")
    public void calcuPPGZ(SimpleRequest request) {
        stockCalcuResultRepository.exec("begin P_SAVE_CXPPGZ(); end;");
    }



    // 测算车型 - 返回可测算的仓库
    private boolean returnCalcuLocation() {
        String sql = "" +
                "SELECT COUNT(1) " +
                "  FROM MAS_CALCULATED_STATE " +
                " WHERE VIP_MODEL = 2 HAVING " +
                " COUNT(1) = (SELECT COUNT(1) FROM MAS_CALCULATED_STATE WHERE DATA_CENTER_MODEL_MARK2 = 0 AND DATA_CENTER_MODEL_MARK = 0) ";
        List canCalcuLocations = calculatedStateRepository.findAll(sql);
        if (!canCalcuLocations.isEmpty()) {
            return Integer.valueOf(canCalcuLocations.get(0).toString()) == 0?false:true;
        }
        return canCalcuLocations.size() != 0;
    }

    // 测算车型 - 匹配数据中心和车型
    @Listener(topic = "event:ssm.service.requestSynCalcuStock")
    public void requestSynCalcuStock(SimpleRequest request) {
        Map condi = request.getMap();
        stockCalcuResultRepository.exec("begin P_CXDZML_SCHEDULER('"+ condi.get("LOCATION_ID") +"'); end;");
    }

    // 测算会员 - 获取 app 车型数据是否全部完成
    public int vipModelIsDone() {
        return calculatedStateRepository.findAll("SELECT ID FROM MAS_CALCULATED_STATE T WHERE VIP_MODEL = 0 AND ROWNUM = 1").size();
    }

    // 创建车型结果表
    @Listener(topic = "event:ssm.service.createStockResult")
    public void createStockResult() {
        stockCalcuResultRepository.exec("begin P_CREATE_STOCK_RESULT_T(); end;");
    }


    // 计算数据 - 每隔 20 分钟获取一次数据
    public void calcuStockResult() {
        // 获取未开始计算的数据
      /*  List<Map> findUndones = calculatedStateRepository.findUndone();
        findUndones.forEach(singData -> {
            Map findUndoneM = (Map) singData;
            this.aynUpdateStockResult(findUndoneM);
        });*/
      List<CalculatedState> dataCenterModelComplete = calculatedStateRepository.findAll(JpaDsl.toCriteriaByEq("dataCenterModelMark2", 2, "dataCenterModelMark", 2));
      if (dataCenterModelComplete.size() == 0) return;
      List<CalculatedState> performStates = calculatedStateRepository.findAll("SELECT 1 FROM MAS_CALCULATED_STATE WHERE CALCU_QUALIFY_MARK != 0 ");
      if (performStates.size() == 0) stockCalcuResultRepository.exec("BEGIN P_PERFORM_STOCK_CALCU_R(); END;");
    }

    // 计算数据 - 开始计算
    @Listener(topic = "ssm.safetyStockCalcuService.synUpdateStockResult")
    public void aynUpdateStockResult(/*SimpleRequest request*/Map getJO) {
//        JSONObject getJO = request.getJO();
        String locationId = getJO.get("LOCATION_ID").toString();

        // 排除在进行中的数据
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_QUALIFY_MARK", 1, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_SALES_MARK", 1, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_COVERAGE_MARK", 1, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_STOCK_QTY_MARK", 1, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_STOCK_PRO_MARK", 1, "LOCATION_ID", locationId));


        stockCalcuResultRepository.exec(/*this.serialNumber(locationId)*/"begin P_I_PW('"+ locationId +"'); end;");
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_QUALIFY_MARK", 2, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_COVERAGE_MARK", 2, "LOCATION_ID", locationId));

        stockCalcuResultRepository.exec(/*this.matchSales(locationId)*/"BEGIN P_I_MATCH_SALES('"+ locationId +"'); END;");
        stockCalcuResultRepository.exec(/*this.actualSales(locationId)*/"BEGIN P_I_ACTUAL_SALES('"+ locationId +"'); END;");
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_SALES_MARK", 2, "LOCATION_ID", locationId));

        stockCalcuResultRepository.exec(/*this.updateStoreTotal()*/"BEGIN P_I_STORE_TOTAL('"+ locationId +"'); END;");
        stockCalcuResultRepository.exec(/*this.scoreTotalSort()*/"BEGIN P_I_SCORE_TOTAL_SORT('"+ locationId +"'); END;");
        stockCalcuResultRepository.exec(/*this.vipCover(locationId)*/"BEGIN P_I_VIP_COVER('"+locationId+"'); END;"); // 去重会员车型后汇总会员
        stockCalcuResultRepository.exec(this.updateVipQty(locationId)/*"BEGIN P_I_VIP_QTY(); END; "*/);
        stockCalcuResultRepository.exec(/*this.safetyStockPro()*/"begin P_PUT_STOCK_PRO('"+locationId+"'); end;");
        stockCalcuResultRepository.exec(/*this.safetyStockQty()*/"begin P_CALCU_STOCK_QTY('"+ locationId +"'); end;");
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_STOCK_PRO_MARK", 2, "LOCATION_ID", locationId));
        this.sendAynEvent("ssm.calculatedStateService.updateState", MapUtil.mapper("CALCU_STOCK_QTY_MARK", 2, "LOCATION_ID", locationId));
    }

    @Listener(topic = "ssm.safetyStockCalcuService.synUpdateStockResult2")
    public void aynUpdateStockResult2(SimpleRequest request) { // 多余代码
        JSONObject getJO = request.getJO();
        String locationId = getJO.getString("LOCATION_ID");

        Class<SafetyStockCalcuService> safetyStockCalcuService = SafetyStockCalcuService.class;

        Method[] method = safetyStockCalcuService.getDeclaredMethods();
        List<Map<String, CalcuStateStateAnno>> annos = new ArrayList<>();
        for (Method m : method) {
            if (!m.isAnnotationPresent(CalcuStateStateAnno.class)) continue;
            annos.add(MapUtil.mapper(m.getName(), m.getAnnotation(CalcuStateStateAnno.class)));
        }
        annos.sort(Comparator.comparing(map -> map.entrySet().iterator().next().getValue().qualifying()));

        annos.forEach(requestMethod -> {
            Map annoMethod = (Map) requestMethod;
            annoMethod.forEach((k, v) -> {
                CalcuStateStateAnno calcuStateAnno = (CalcuStateStateAnno) v;
                if (StringUtils.hasText(calcuStateAnno.toptic())) this.sendAynEvent(calcuStateAnno.toptic(), MapUtil.mapper(calcuStateAnno.columnName(), 1, "LOCATION_ID", locationId)); // 开始计算异步更新状态表

                try {
                    Method currentMethod = safetyStockCalcuService.getMethod(String.valueOf(k), String.class); // 参数可能为空
                    String sql = (String) currentMethod.invoke(safetyStockCalcuService.newInstance(), locationId); // 计算具体逻辑 sql
                    stockCalcuResultRepository.exec(sql);
                    if (StringUtils.hasText(calcuStateAnno.toptic())) this.sendAynEvent(calcuStateAnno.toptic(), MapUtil.mapper(calcuStateAnno.columnName(), 2, "LOCATION_ID", locationId)); // 完成更新状态表
                    System.out.println("----------------");
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new RuntimeException("--------");
                }
            });
        });
    }


    // 排位
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_QUALIFY_MARK", qualifying = 1)
    public String serialNumber(String locationId) {
        String id = this.sequenceName("stock_calcu_result");
        return "" +
                " INSERT INTO STOCK_CALCU_RESULT " +
                "  (ID, LOCATION_ID, PRODUCT_ID, FITTING_MESSAGE_ID, BRAND_PRIORITY_ID, " +
                "   BRAND_PRIORITY_SERIAL_NUMBER, COUNT_MODEL, ACTIVE_MEMBERS_NUMBER, " +
                "   MODEL_SERIAL_NUMBER) " +
                "  SELECT "+ id +", A.* " +
                "    FROM (SELECT A.LOCATION_ID, A.PRODUCT_ID, FITTING_MESSAGE_ID, " +
                "                  A.BRAND_PRIORITY, A.BRAND_PRIORITY_SERIAL_NUMBER, " +
                "                  COUNT(PZCXID) COUNT_MODEL, " +
                "                  SUM(COUNT_VIP) ACTIVE_MEMBERS_NUMBER, " +
                "                  ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID ORDER BY COUNT(PZCXID) DESC, BRAND_PRIORITY_SERIAL_NUMBER ASC) MODEL_SERIAL_NUMBER " +
                "             FROM (SELECT A.*, " +
                "                           DENSE_RANK() OVER(PARTITION BY PZCXID ORDER BY (SELECT TO_NUMBER(CODE) FROM MAS_ENUMERATION J WHERE J.ID = A.BRAND_PRIORITY)) BRAND_PRIORITY_SERIAL_NUMBER " +
                "                      FROM MAS_STOCK_CACLU_RESULT PARTITION(P"+ locationId +") A) A " +
                "            GROUP BY A.LOCATION_ID, A.PRODUCT_ID, A.BRAND_PRIORITY, " +
                "                     A.BRAND_PRIORITY_SERIAL_NUMBER, FITTING_MESSAGE_ID) A " /*+
                "   WHERE MODEL_SERIAL_NUMBER = 1 "*/;

    }

    // 计算匹配销量
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_SALES_MARK", qualifying = 2)
    public String matchSales(String locationId) {
        return "" +
                "MERGE INTO STOCK_CALCU_RESULT A " +
                "USING (SELECT B.LOCATION_ID, A.DEPT_ID, FITTING_MESSAGE_ID, " +
                "              SUM(COUNT_SALES) MATCH_SALES " +
                "         FROM MAS_CALCU_PRO_SALES A, V_GET_LOCATION_DEPT B " +
                "        WHERE A.DEPT_ID = B.DEPT_ID " +
                "          AND EXISTS (SELECT 1 " +
                "                 FROM MAS_STOCK_CACLU_RESULT PARTITION(P"+ locationId +") J " +
                "                WHERE J.LOCATION_ID = B.LOCATION_ID " +
                "                  AND J.PZCXID = A.JEKUN_MODEL_CODE " +
                "                  AND J.PRODUCT_ID = A.PRODUCT_ID) " +
                "        GROUP BY B.LOCATION_ID, A.DEPT_ID, FITTING_MESSAGE_ID) B " +
                "ON (A.LOCATION_ID = B.LOCATION_ID AND A.FITTING_MESSAGE_ID = B.FITTING_MESSAGE_ID) " +
                "WHEN MATCHED THEN " +
                "  UPDATE " +
                "     SET A.MATCH_SALES = B.MATCH_SALES ";
    }

    // 计算实际销量
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_SALES_MARK", qualifying = 3)
    public String actualSales(String locationId) {
         return "" +
                "MERGE INTO STOCK_CALCU_RESULT A " +
                "USING (SELECT B.LOCATION_ID, A.DEPT_ID, PRODUCT_ID, FITTING_MESSAGE_ID, " +
                "              SUM(COUNT_SALES) ACTUAL_SALES " +
                "         FROM MAS_CALCU_PRO_SALES A, V_GET_LOCATION_DEPT B " +
                "        WHERE A.DEPT_ID = B.DEPT_ID " +
                "          AND EXISTS (SELECT 1 " +
                "                 FROM MAS_STOCK_CACLU_RESULT PARTITION(P"+ locationId +") J " +
                "                WHERE J.LOCATION_ID = B.LOCATION_ID " +
                "                  AND J.PZCXID = A.JEKUN_MODEL_CODE " +
                "                  AND J.PRODUCT_ID = A.PRODUCT_ID) " +
                "        GROUP BY B.LOCATION_ID, A.DEPT_ID, PRODUCT_ID, FITTING_MESSAGE_ID) B " +
                "ON (A.LOCATION_ID = B.LOCATION_ID AND A.PRODUCT_ID = B.PRODUCT_ID) " +
                "WHEN MATCHED THEN " +
                "  UPDATE " +
                "     SET ACTUAL_SALES = B.ACTUAL_SALES ";
    }

    // 计算覆盖率
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_COVERAGE_MARK", qualifying = 4)
    public String vipCover(String locationId) {
         return "" +
                " MERGE INTO STOCK_CALCU_RESULT A " +
                "USING (SELECT A.LOCATION_ID, A.PRODUCT_ID, " + // SCORE_TOTAL_SORT,
                "              MAX(DIS_ALL_SUM_VIP) DIS_ALL_SUM_VIP, " +
                "              (SELECT COUNT(1) FROM STOCK_STORE_MEMBER_MODELS WHERE LOCATION_ID = A.LOCATION_ID) ALL_SUM_VIP " +
//                "              MAX(ALL_SUM_VIP) ALL_SUM_VIP " +
                "         FROM (SELECT A.*, " +
                "                       SUM((CASE " +
                "                             WHEN ACCORD_SCORE_TATAL_SORT > 1 THEN " +
                "                              0 " +
                "                             ELSE " +
                "                              COUNT_VIP " +
                "                           END)) OVER(PARTITION BY A.LOCATION_ID, FITTING_MESSAGE_ID, ACCORD_SCORE_TATAL_SORT ORDER BY SCORE_TOTAL_SORT) DIS_ALL_SUM_VIP " +
//                "                       , SUM(COUNT_VIP) OVER(PARTITION BY A.LOCATION_ID ORDER BY 1) ALL_SUM_VIP " +
                "                  FROM (SELECT A.LOCATION_ID, A.PZCXID, A.PRODUCT_ID, " +
                "                                A.FITTING_MESSAGE_ID, SCORE_TOTAL_SORT, COUNT_VIP, " +
                "                                ROW_NUMBER() OVER(PARTITION BY A.LOCATION_ID, A.FITTING_MESSAGE_ID, PZCXID ORDER BY SCORE_TOTAL_SORT) ACCORD_SCORE_TATAL_SORT " +
                "                           FROM MAS_STOCK_CACLU_RESULT PARTITION(P"+ locationId +") A, " +
                "                                V_STOCK_CALCU_RESULT B " +
                "                          WHERE A.LOCATION_ID = B.LOCATION_ID " +
                "                            AND A.PRODUCT_ID = B.PRODUCT_ID " +
                "                            AND A.FITTING_MESSAGE_ID = B.FITTING_MESSAGE_ID) A) A " +
                "        GROUP BY A.LOCATION_ID, A.PRODUCT_ID) B " + // , SCORE_TOTAL_SORT
                "ON (A.LOCATION_ID = B.LOCATION_ID AND A.PRODUCT_ID = B.PRODUCT_ID) " +
                "WHEN MATCHED THEN " +
                "  UPDATE " +
                "     SET A.ALL_SUM_VIP = B.ALL_SUM_VIP, " +
                "         A.DIS_ALL_SUM_VIP = B.DIS_ALL_SUM_VIP ";
    }

    // 计算总分
    private String updateStoreTotal() {
        return " UPDATE STOCK_CALCU_RESULT SET SCORE_TOTAL = (NVL(MATCH_SALES, 0) * 0.6) + (NVL(ACTIVE_MEMBERS_NUMBER, 0) * 0.4) ";
    }

    // 总分排序
    @CalcuStateStateAnno(qualifying = 5)
    public String scoreTotalSort() {
         return " UPDATE STOCK_CALCU_RESULT A SET SCORE_TOTAL_SERIAL_NUMBER = (SELECT SCORE_TOTAL_SORT FROM V_STOCK_CALCU_RESULT J WHERE J.LOCATION_ID = A.LOCATION_ID AND J.PRODUCT_ID = A.PRODUCT_ID AND ROWNUM = 1) ";
    }

    // 去重 vip 计算 - 重复的取上一行不为 0 的数据
    @CalcuStateStateAnno(qualifying = 6)
    public String updateVipQty(String locationId) {
//        return " SELECT (CASE WHEN DIS_ALL_SUM_VIP = 0 THEN F_ZERO_GET_VIP(SCORE_TOTAL_SERIAL_NUMBER, LOCATION_ID)  ELSE DIS_ALL_SUM_VIP END)  FROM STOCK_CALCU_RESULT ";
        return "SELECT (CASE " +
                "            " +
                "             WHEN NVL2(DIS_ALL_SUM_VIP, DIS_ALL_SUM_VIP, 0) = 0 THEN " +
                "              F_ZERO_GET_VIP(SCORE_TOTAL_SERIAL_NUMBER, LOCATION_ID, " +
                "                             FITTING_MESSAGE_ID) " +
                "             ELSE " +
                "              DIS_ALL_SUM_VIP " +
                "           END) " +
                "      FROM STOCK_CALCU_RESULT WHERE LOCATION_ID = '" + locationId + "' " +
                "     ORDER BY FITTING_MESSAGE_ID, SCORE_TOTAL_SERIAL_NUMBER";
    }

    // 计算安全库存商品数量
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_STOCK_PRO_MARK", qualifying = 7)
    public String safetyStockPro() {
        return "begin P_PUT_STOCK_PRO(); end;";
    }

    // 计算安全库存商品限量
    @CalcuStateStateAnno(toptic = "ssm.calculatedStateService.updateState", columnName = "CALCU_STOCK_QTY_MARK", qualifying = 8)
    public String safetyStockQty() {
        return "begin P_CALCU_STOCK_QTY(); end;";
    }

    // 获取序列编码
    public String sequenceName(String sequenceCode) {
        List sequenceIds = SpringManager.getBean(SequenceRepository.class).findAll("SELECT ID FROM IR_SEQUENCE WHERE CODE = '"+ sequenceCode +"' ");
        return String.format("ir_sequence_%05d.NEXTVAL", Long.valueOf(sequenceIds.get(0).toString()));
    }

    // 完成测算，清空数据
    public void calcuClear() {
        stockCalcuResultRepository.exec("BEGIN P_STOCK_CALCU_CLEAR(); END;");
    }


    // 导入会员 vip
    @PersistenceContext
    private EntityManager entityManager;
    private final int BATCH_SIZE = 900;
    private final List FIXED_TITLES = Arrays.asList("仓库", "车牌号码", "配置车型ID", "门店", "会员等级");
    private final List VIP_LIVEN_REFER = Arrays.asList("新会员", "活跃会员", "非活跃会员", "将流失会员", "已流失会员", "非常活跃会员", "无消费会员"); // 需查看下标与值是否一致
    public Map importVipModel(SimpleRequest request) {
        // 增加判断界面开关是否是开
        JSONArray tabData = new JSONArray();
        JSONObject importData = request.getJO().getJSONObject("importData");
        for (Map.Entry<String, Object> m: importData.entrySet()) {
            tabData = (JSONArray) m.getValue(); // 只会有一个页签, 不考虑多次循环问题, 只为解决页签名称可能会被修改
        }
        if (tabData.size() <= 1) throw new RuntimeException("请检查导入文件中是否有添加数据！");

        Set disLocationIds = new HashSet();
        for (int i = 0, iLen = tabData.size(); i < iLen; i++) {
            JSONArray importSingleData = tabData.getJSONArray(i);
            if (i == 0) {
                this.judgeImportTitle(importSingleData);
                continue;
            }
            StoreMemberModelsImport iStoreMenmber = appendImportData(importSingleData);
            entityManager.persist(iStoreMenmber);

            if (i % BATCH_SIZE == 0) {
                entityManager.flush();
                entityManager.clear();
            }
            disLocationIds.add(importSingleData.get(0));
        }
        entityManager.flush();
        entityManager.clear();

        return MapUtil.mapper("allVipSize", tabData.size(), "locationValidSize", disLocationIds.size());
    }

    // 导入会员 vip - 判断导入列是否符合规则
    private void judgeImportTitle(JSONArray importTitles) {
        SpringManager.getBean(StoreMemberModelsImportRepository.class).exec(" TRUNCATE TABLE STOCK_STORE_MEMBER_MODELS_I ");
        List importTitleLs = new ArrayList();
        importTitles.stream().forEach(title -> importTitleLs.add(title));
        if (!importTitleLs.equals(FIXED_TITLES)) throw new RuntimeException("请按导出文件列导入！");
    }

    // 导入vip - 拼接保存数据
    private StoreMemberModelsImport appendImportData(JSONArray importSingleData) {
        StoreMemberModelsImport iStoreMenmber = new StoreMemberModelsImport();
        iStoreMenmber.setId(String.valueOf(Math.random()));
        iStoreMenmber.setLocationId(importSingleData.get(0).toString());
        iStoreMenmber.setCarLicense(importSingleData.get(1).toString());
        iStoreMenmber.setJekunModelCode(importSingleData.get(2).toString());
        iStoreMenmber.setStoreCode(importSingleData.get(3).toString());
        String vipLivenNumber = transforVipLive(importSingleData.get(4));
        if (StringUtils.hasText(vipLivenNumber)) iStoreMenmber.setVipLivenessId(vipLivenNumber);
        return iStoreMenmber;
    }

    // 导入vip - vip级别中文转换
    private String transforVipLive(Object vipLivenessId) {
        if (Objects.isNull(vipLivenessId)) return "";
        int vipLiveIndex = VIP_LIVEN_REFER.indexOf(vipLivenessId);
        if (vipLiveIndex >= 0) return String.valueOf(vipLiveIndex);
        return "";
    }


}






































